package com.gisuni.sddc.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.json.JSONArray;
import org.json.JSONObject;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import com.gisuni.sddc.model.BaseUser;
import com.gisuni.sddc.model.UserOrganization;
import com.gisuni.sddc.model.Userinfo;

@IocBean
public class UserOrganizationDao {
	@Inject
	private Dao dao;  
	
 
	public BaseUser userupdata(BaseUser baseUser){
		dao.update(baseUser);
		return baseUser;
	}
 
    public Boolean updateUserOrganization(String updateSql ){
    	
    	Boolean result = false;
    	Sql sql = Sqls.create(updateSql);
	    sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				return true;
			}
		});
	    dao.execute(sql);
	    return  sql.getObject(Boolean.class );
 
    }
 
	public String queryBaseUserAll(int from,int to,String selectSql){
		Sql sql = Sqls.create(selectSql);
		final Map map = getQuota();
	    sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				JSONArray result=new JSONArray();
			     while (rs.next()){
			    	 JSONObject ret=new JSONObject();
			    	 	ret.put("organizationBusinessName", rs.getString("organizationBusinessName"));
			    		ret.put("organizationDevelopName", rs.getString("organizationDevelopName"));
			    		ret.put("organizationCompanyName", rs.getString("organizationCompanyName"));
			    	    ret.put("displayName", rs.getString("displayname"));
				    	ret.put("userId",rs.getString("userid"));
				    	ret.put("userName", rs.getString("username"));
				    	ret.put("quota", rs.getString("quota"));
				    	String organizationDevelopId = rs.getString("organizationDevelopId");
				    	ret.put("organizationDevelopId", organizationDevelopId);
				    	Iterator iterator = map.keySet().iterator();
				    	if(rs.getString("quota")!=null && !"0".equals(rs.getString("quota")))
				    	while(iterator.hasNext()){
				    		String key = (String) iterator.next();
				    		if(key.equals(organizationDevelopId)){
				    			ret.put("quotaPercent", map.get(key));
				    		}
				    	}
			    	 result.put(ret);
			     }
				return result.toString();
			}
		});
	    dao.execute(sql);
	    return  sql.getObject(String.class);
	}
	
	//扩展组-满额率
	public Map getQuota(){
		StringBuffer sbf = new StringBuffer();
		/*
		 select t.organization_develop_id, 
		 case (t2.num / t.quota)||'' when '0' then '0' else  substr(((t2.num / t.quota)*100)||'',0,2)||'%' end
		  from organization_user_quota t
		  left join (select organization_develop_id, count(1) num from user_organization t1
		               
              group by t1.organization_develop_id) t2 on t.organization_develop_id = t2.organization_develop_id
              
		 * */
		sbf.append("select t.organization_develop_id developId, ");
		sbf.append("case (t2.num / t.quota)||'' when '0' then '0' else  substr(((t2.num / t.quota)*100)||'',0,2)||'%' end percentage ");
		sbf.append("from SDDCGISPLATFORM.organization_user_quota t ");
		sbf.append("left join (select organization_develop_id, count(1) num from SDDCGISPLATFORM.user_organization t1 ");
		sbf.append("  group by t1.organization_develop_id) t2 on t.organization_develop_id = t2.organization_develop_id");
		Sql sql = Sqls.create(sbf.toString());
	    sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				Map<String,String> map = new HashMap<String,String>();
			     while (rs.next()){
			    	 map.put( rs.getString("developId"),  rs.getString("percentage"));
			     }
				return map;
			}
		});
	    dao.execute(sql);
	    return  sql.getObject(Map.class);
	}
 
	public int queryBaseUsertotal(String str){
//		int num = dao.count("SDDCONEMAP.USERINFO");
		Sql sql = Sqls.create("select count(1) sizes from SDDCONEMAP.USERINFO"+str);
	    sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				  String result ="";
			     while (rs.next()){
			    	 	  result = rs.getString("sizes") ;
			     }
			     return result;
			}
		});
	    dao.execute(sql);
		String t =   sql.getObject(String.class);
	    return Integer.parseInt(t);
	}

	public String queryOrganization(String  selectSql) {
		Sql sql = Sqls.create(selectSql);
	    sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql)
					throws SQLException {
				JSONArray result=new JSONArray();
			     while (rs.next()){
			    	 JSONObject ret=new JSONObject();
			    	 	ret.put("organizationName", rs.getString("organization_name"));
				    	ret.put("id", rs.getString("id"));
			    	    result.put(ret);
			     }
				return result.toString();
			}
		});
	    dao.execute(sql);
	    return  sql.getObject(String.class);
	}
	
	public Boolean addUserOrganization(UserOrganization userOrganization) {
 
		UserOrganization result = dao.insert(userOrganization);
	    if(result!=null){
	    	return true;
	    }
		return false;
	}
 
	
}
